import tkinter as tk
from tkinter import filedialog, simpledialog
import pandas as pd

def select_file():
    root = tk.Tk()
    root.withdraw()  # 隐藏主窗口
    file_path = filedialog.askopenfilename(
        title="选择Excel文件",
        filetypes=[("Excel files", "*.xlsx *.xls")]
    )
    print(f"选择的文件: {file_path}")
    return file_path

def get_keyword():
    root = tk.Tk()
    root.withdraw()
    keyword = tk.simpledialog.askstring("输入关键词", "请输入要查找的sheet页关键词:")
    print(f"使用的关键词: {keyword}")
    return keyword

def get_output_filename(default_name):
    root = tk.Tk()
    root.withdraw()
    filename = tk.simpledialog.askstring("输出文件名", 
                                       "请输入输出文件名（不带扩展名）:",
                                       initialvalue=default_name)
    return f"{filename}.xlsx"

def merge_bom_sheets(input_file, output_file, keyword):
    # 读取Excel文件
    xls = pd.ExcelFile(input_file)
    
    # 获取所有包含关键词的sheet名称
    bom_sheets = [sheet for sheet in xls.sheet_names if keyword.upper() in sheet.upper()]
    
    if not bom_sheets:
        print("没有找到包含'BOM'的sheet页")
        return
    
    # 读取并合并所有BOM sheet
    dfs = []
    for sheet in bom_sheets:
        df = pd.read_excel(xls, sheet_name=sheet)
        df['Source Sheet'] = sheet  # 添加来源sheet列
        dfs.append(df)
    
    merged_df = pd.concat(dfs, ignore_index=True)
    
    # 写入新的Excel文件
    with pd.ExcelWriter(output_file) as writer:
        merged_df.to_excel(writer, index=False, sheet_name='Merged BOM')
    
    print(f"成功合并{len(bom_sheets)}个sheet页到 {output_file}")

if __name__ == "__main__":
    input_file = select_file()
    if input_file:
        keyword = get_keyword()
        if keyword:
            default_name = input_file.replace(".xlsx", "_merged").split("/")[-1]
            output_file = get_output_filename(default_name)
            print(f"输出文件将保存为: {output_file}")
            merge_bom_sheets(input_file, output_file, keyword)
        else:
            print("未输入关键词，程序退出")
    else:
        print("未选择文件，程序退出")
